﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo623
{
    public partial class Index : System.Web.UI.Page
    {
        //整个类都需要使用 工具类 所以创建在这里方便调用
        SQLHelper helper = new SQLHelper();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

                /*
                 * 建立数据库连接
                 * 1.写好数据库登录信息
                 * 2.创建连接类
                 * 3.打开连接
                 * 4。准备sql语句
                 * 5.将参数添加进语句
                 * 6.使用command类执行语句并获取结果 
                 * 7.处理结果
                 * 8.关闭连接
                 * 
                 * 步骤比较多  不要慌
                 */

                DataSet ds = null;
                //1.准备数据库连接信息

                string constr = "server=.;uid=sa;pwd=123456;database=Student_db;";

                //2.创建连接类 传递连接信息
                SqlConnection con = new SqlConnection(constr);//

                /* 因为连接时可能出错 所以用try捕捉*/
                try
                {
                    //3.打开连接
                    con.Open();

                    //4.准备sql语句
                    string sql = "select * from StudentInfo";

                    //5.将参数添加进语句(这里不需要参数 所以不写)

                    //6.使用SqlDataAdapter类执行语句并获取结果

                    SqlDataAdapter sda = new SqlDataAdapter(sql, con);
                    ds = new DataSet();
                    sda.Fill(ds);

                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    //8.关闭连接
                    if (con != null)
                    {
                        con.Close();
                    }
                }

                //7.处理结果
                GridView1.DataSource = ds.Tables[0];
                GridView1.DataBind();
            }
        }
        
        protected void Button1_Click(object sender, EventArgs e)
        {
            /*
             搜索功能：
                 获取到用户输入的文本 
                 执行sql语句
                 将结果打印在GridView控件中
             */
            DataSet ds = null;
            //1.准备数据库连接信息

            string constr = "server=.;uid=sa;pwd=123456;database=Student_db;";

            //2.创建连接类 传递连接信息
            SqlConnection con = new SqlConnection(constr);//

            /* 因为连接时可能出错 所以用try捕捉*/
            try
            {
                //3.打开连接
                con.Open();

                string name = TextBox1.Text;
                //4.准备sql语句
                string sql = "select * from StudentInfo where stu_name like '%'+@name+'%'";

                //5.使用Command类将参数添加进语句
                SqlParameter[] pars = {
                    new SqlParameter("name",name)
                };
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddRange(pars);


                //6.使用SqlDataAdapter类执行语句并获取结果
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                ds = new DataSet();
                sda.Fill(ds);

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                //8.关闭连接
                if (con != null)
                {
                    con.Close();
                }
            }

            //7.处理结果
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string constr = "server=.;uid=sa;pwd=123456;database=Student_db;";

            //2.创建连接类 传递连接信息
            SqlConnection con = new SqlConnection(constr);//

            /* 因为连接时可能出错 所以用try捕捉*/
            try
            {
                //3.打开连接
                con.Open();

                //4.准备sql语句
                string name = UserName.Text;
                string pwd = Password.Text;
                string email = Email.Text;
                string intro = Intro.Text;

                string sql = "insert into StudentInfo (stu_name,password,email,intro) values (@name,@pwd,@email,@intro)";
                //5.添加对应参数
                SqlParameter[] pars =
                {
                new SqlParameter("@name",name),
                new SqlParameter("@pwd",pwd),
                new SqlParameter("@email",email),
                new SqlParameter("@intro",intro)
                };

                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddRange(pars);
                //执行sql语句 获取被影响的行数
                int result = cmd.ExecuteNonQuery();



                if (result != -1)
                {
                    string sql1 = "select * from StudentInfo";
                    SqlDataAdapter sda = new SqlDataAdapter(sql1, con);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    GridView1.DataSource = ds.Tables[0];
                    GridView1.DataBind();
                }
                else
                {
                    Response.Write("添加失败");
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                //8.关闭连接
                if (con != null)
                {
                    con.Close();
                }
            }


        }

        protected void Button1_Click1(object sender, EventArgs e)
        {
            string name = TextBox1.Text;
            string sql = "select * from StudentInfo where stu_name like '%'+@name+'%'";
            SqlParameter[] pars = {
                    new SqlParameter("name",name)
                };
            GridView1.DataSource = helper.Get(sql, pars);
            GridView1.DataBind();
        }

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {

            GridView1.EditIndex = -1;
            GvBind();
        }

        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            string stu_id = GridView1.DataKeys[e.RowIndex].Value.ToString();
            string sql = "delete from StudentInfo where stu_id=@stu_id";
            SqlParameter[] pars = {
                new SqlParameter("stu_id",stu_id)
            };
            int result = helper.Set(sql, pars);
            if (result != -1)
            {
                GvBind();
            }
            else
            {
                Response.Write("删除失败！");
            }
        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            GvBind();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {

            string id = GridView1.DataKeys[e.RowIndex].Value.ToString();
            string name = (GridView1.Rows[e.RowIndex].Cells[0].Controls[0] as TextBox).Text;
            string pwd = (GridView1.Rows[e.RowIndex].Cells[1].Controls[0] as TextBox).Text;
            string email = (GridView1.Rows[e.RowIndex].Cells[2].Controls[0] as TextBox).Text;
            string intro = (GridView1.Rows[e.RowIndex].Cells[3].Controls[0] as TextBox).Text;

            string sql = "update  StudentInfo set stu_name=@name,password=@pwd,email=@email,intro=@intro where stu_id=@id";
            SqlParameter[] pars =
           {
                new SqlParameter("@name",name),
                new SqlParameter("@pwd",pwd),
                new SqlParameter("@email",email),
                new SqlParameter("@intro",intro),
                new SqlParameter("@id",id)
            };
            int result = helper.Set(sql, pars);
            if (result != -1)
            {
                GridView1.EditIndex = -1;
                GvBind();
            }
            else
            {
                Response.Write("修改失败！");
            }
        }

        private void GvBind()
        {
            string sql = "select * from StudentInfo";
            GridView1.DataSource = helper.Get(sql, null);
            GridView1.DataKeyNames = new string[] { "stu_id" };
            GridView1.DataBind();
        }
    }
}